package com.etc.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.etc.dao.GamesDao;
import com.etc.entity.Business;
import com.etc.entity.Games;
import com.etc.entity.GamesType;
import com.etc.util.DBUtil;

public class GamesDaoImpl implements GamesDao{
	/**
	 * 查询上架日期最新的四个游戏
	 */
	@Override
	public List<Games> topFour() {
		List<Games> list =  new ArrayList<Games>();
		ResultSet rs = DBUtil.query("select games_id,games_name,price,type_id,status,image,content,business_id,onsale_time from games order by onsale_time desc limit 4;");
		try {
			while (rs.next()) {
				Integer gamesId = rs.getInt("games_id");  
				String gamesName = rs.getString("games_name"); 
				BigDecimal price = rs.getBigDecimal("price"); 
				Integer typeId = rs.getInt("type_id");   
				Integer status = rs.getInt("status");   
				String image = rs.getString("image");     
				String content = rs.getString("content");   
				Integer businessId = rs.getInt("business_id");
				String onsaleTime = rs.getString("onsale_time");
				
				// 将每一条记录封装成一个Java对象
				Games games = new Games();
				games.setGamesId(gamesId);
				games.setGamesName(gamesName);
				games.setPrice(price);
				games.setTypeId(typeId);
				games.setStatus(status);
				games.setImage(image);
				games.setContent(content);
				games.setBusinessId(businessId);
				games.setOnsaleTime(onsaleTime);
				
				// 存储到集合中
				list.add(games);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}
	
	/**
	 * 查询好评次数最多的前六个游戏
	 */
	@Override
	public List<Games> topSix() {
		
		List<Games> list =  new ArrayList<Games>();
		ResultSet rs = DBUtil.query("select g.games_id,g.games_name,g.price,g.image,count(d.comment) from games g left join discuss d on g.games_id = d.games_id where comment = 1 group by games_id limit 6");
		try {
			while (rs.next()) {
				Integer gamesId = rs.getInt("games_id");  
				String gamesName = rs.getString("games_name"); 
				BigDecimal price = rs.getBigDecimal("price");    
				String image = rs.getString("image");     
				Integer goodReputation = rs.getInt("count(d.comment)");
				
				// 将每一条记录封装成一个Java对象
				Games games = new Games();
				games.setGamesId(gamesId);
				games.setGamesName(gamesName);
				games.setPrice(price);
				games.setImage(image);
				games.setGoodReputation(goodReputation);
				
				// 存储到集合中
				list.add(games);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}
	/**
	 * 通过游戏编号查询
	 */
	@Override
	public Games findByGamesId(Integer gamesId) {
		ResultSet rs = DBUtil.query("select g.games_id,g.games_name,g.price,g.type_id,g.status,g.image,g.content,g.business_id,g.onsale_time,t.type_name,b.nickname from games g left join games_type t on g.type_id = t.type_id left join business b on g.business_id = b.business_id where games_id = ?", gamesId);
		Games games = null;
		try {
			while (rs.next()) {
				String gamesName = rs.getString("games_name"); 
				BigDecimal price = rs.getBigDecimal("price"); 
				Integer typeId = rs.getInt("type_id");   
				Integer status = rs.getInt("status");   
				String image = rs.getString("image");     
				String content = rs.getString("content");   
				Integer businessId = rs.getInt("business_id");
				String onsaleTime = rs.getString("onsale_time");
				String typeName = rs.getString("type_name");
				String nickname = rs.getString("nickname");
				// 将每一条记录封装成一个Java对象
				games = new Games();
				games.setGamesId(gamesId);
				games.setGamesName(gamesName);
				games.setPrice(price);
				games.setTypeId(typeId);
				games.setStatus(status);
				games.setImage(image);
				games.setContent(content);
				games.setBusinessId(businessId);
				games.setOnsaleTime(onsaleTime);
				
				GamesType gamesType = new GamesType();
				gamesType.setTypeName(typeName);
				games.setGamesType(gamesType);
				
				Business business = new Business();
				business.setNickname(nickname);
				games.setBusiness(business);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return games;
	}
	
	/**
	 * 查询所有游戏
	 */
	@Override
	public List<Games> findAll() {
		List<Games> list = new ArrayList<Games>();
		ResultSet rs = DBUtil.query("select g.games_id, g.games_name, g.price, g.type_id, g.status, g.image, g.content, g.business_id, g.onsale_time, t.type_name from games g left join games_type t on g.type_id = t.type_id");
		try {
			while (rs.next()) {
				Integer gamesId = rs.getInt("games_id");
				String gamesName = rs.getString("games_name"); 
				BigDecimal price = rs.getBigDecimal("price"); 
				Integer typeId = rs.getInt("type_id");   
				Integer status = rs.getInt("status");   
				String image = rs.getString("image");     
				String content = rs.getString("content");   
				Integer businessId = rs.getInt("business_id");
				String onsaleTime = rs.getString("onsale_time");
				String typeName = rs.getString("type_name");
				
				// 将每一条记录封装成一个Java对象
				Games games = new Games();
				games.setGamesId(gamesId);
				games.setGamesName(gamesName);
				games.setPrice(price);
				games.setTypeId(typeId);
				games.setStatus(status);
				games.setImage(image);
				games.setContent(content);
				games.setBusinessId(businessId);
				games.setOnsaleTime(onsaleTime);
				
				GamesType gamesType = new GamesType();
				gamesType.setTypeName(typeName);
				games.setGamesType(gamesType);
				
				// 存储到集合中
				list.add(games);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 根据类型，价格，名称实现组合查询
	 */
	@Override
	public List<Games> findAllByCondition(Integer typeId, BigDecimal minPrice,BigDecimal maxPrice, String gamesName) {
		List<Games> list = new ArrayList<Games>();
		StringBuilder sql = new StringBuilder("select g.games_id, g.games_name, g.price, g.type_id, g.status, g.image, g.content, g.business_id, g.onsale_time, t.type_name from games g left join games_type t on g.type_id = t.type_id where 1=1 ");
		
		if(typeId!=null&&typeId!=0) {
			sql.append(" and g.type_id = ").append(typeId);
		}
		if(minPrice!=null) {
			sql.append(" and g.price >= ").append(minPrice);
		}
		if(maxPrice!=null) {
			sql.append(" and g.price <= ").append(maxPrice);
		}
		if(gamesName!=null&&!"".equals(gamesName)) {
			sql.append(" and g.games_name like '").append(gamesName).append("'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				Integer gamesId = rs.getInt("games_id");
				gamesName = rs.getString("games_name"); 
				BigDecimal price = rs.getBigDecimal("price"); 
				typeId = rs.getInt("type_id");   
				Integer status = rs.getInt("status");   
				String image = rs.getString("image");     
				String content = rs.getString("content");   
				Integer businessId = rs.getInt("business_id");
				String onsaleTime = rs.getString("onsale_time");
				String typeName = rs.getString("type_name");
				
				// 将每一条记录封装成一个Java对象
				Games games = new Games();
				games.setGamesId(gamesId);
				games.setGamesName(gamesName);
				games.setPrice(price);
				games.setTypeId(typeId);
				games.setStatus(status);
				games.setImage(image);
				games.setContent(content);
				games.setBusinessId(businessId);
				games.setOnsaleTime(onsaleTime);
				GamesType gamesType = new GamesType();
				gamesType.setTypeName(typeName);
				games.setGamesType(gamesType);
				
				// 存储到集合中
				list.add(games);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return list;
	}

	@Override
	public List<Games> findPageByCondition(Integer typeId, BigDecimal minPrice,BigDecimal maxPrice, String gamesNameKeyWord, int pageNum, int pageSize) {
		List<Games> list = new ArrayList<Games>();
		StringBuilder sql = new StringBuilder("select g.games_id, g.games_name, g.price, g.type_id, g.status, g.image, g.content, g.business_id, g.onsale_time, t.type_name from games g left join games_type t on g.type_id = t.type_id where 1=1 ");
		
		if(typeId!=null&&typeId!=0) {
			sql.append(" and g.type_id = ").append(typeId);
		}
		if(minPrice!=null) {
			sql.append(" and g.price >= ").append(minPrice);
		}
		if(maxPrice!=null) {
			sql.append(" and g.price <= ").append(maxPrice);
		}
		if(gamesNameKeyWord!=null&&!"".equals(gamesNameKeyWord)) {
			sql.append(" and g.games_name like '").append(gamesNameKeyWord).append("'");
		}
		
		sql.append(" limit "+(pageNum-1)*pageSize+"," + pageSize);
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				Integer gamesId = rs.getInt("games_id");
				gamesNameKeyWord = rs.getString("games_name"); 
				BigDecimal price = rs.getBigDecimal("price"); 
				typeId = rs.getInt("type_id");   
				Integer status = rs.getInt("status");   
				String image = rs.getString("image");     
				String content = rs.getString("content");   
				Integer businessId = rs.getInt("business_id");
				String onsaleTime = rs.getString("onsale_time");
				String typeName = rs.getString("type_name");
				
				// 将每一条记录封装成一个Java对象
				Games games = new Games();
				games.setGamesId(gamesId);
				games.setGamesName(gamesNameKeyWord);
				games.setPrice(price);
				games.setTypeId(typeId);
				games.setStatus(status);
				games.setImage(image);
				games.setContent(content);
				games.setBusinessId(businessId);
				games.setOnsaleTime(onsaleTime);
				GamesType gamesType = new GamesType();
				gamesType.setTypeName(typeName);
				games.setGamesType(gamesType);
				
				// 存储到集合中
				list.add(games);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return list;
	}

	@Override
	public int countByCondition(Integer typeId, BigDecimal minPrice, BigDecimal maxPrice, String gamesNameKeyWord) {
		int count = 0;
		StringBuilder sql = new StringBuilder("select count(*) from games g left join games_type t on g.type_id = t.type_id where 1=1 ");
		
		if(typeId!=null&&typeId!=0) {
			sql.append(" and g.type_id = ").append(typeId);
		}
		if(minPrice!=null) {
			sql.append(" and g.price >= ").append(minPrice);
		}
		if(maxPrice!=null) {
			sql.append(" and g.price <= ").append(maxPrice);
		}
		if(gamesNameKeyWord!=null&&!"".equals(gamesNameKeyWord)) {
			sql.append(" and g.games_name like '").append(gamesNameKeyWord).append("'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return count;
		
	}

	@Override
	public List<Games> topFive() {
		ResultSet rs = DBUtil.query("select g.games_id,g.games_name,g.image,g.content,count(*) from order_info i left join order_detail d on d.order_no=i.order_no left join games g on d.games_id=g.games_id where i.status = 1 group by g.games_id order by count(*) desc limit 4");
		List<Games> list = new ArrayList<Games>();
		
		try {
			while (rs.next()) {
				Integer gamesId = rs.getInt("games_id");
				String gamesName = rs.getString("games_name");
				String image = rs.getString("image");
				String content = rs.getString("content");
				
				Games games = new Games();
				games.setGamesId(gamesId);
				games.setGamesName(gamesName);
				games.setImage(image);
				games.setContent(content);
				
				list.add(games);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}
	

}
